package export;

import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;

import jxl.Workbook;
import jxl.format.Border;
import jxl.format.UnderlineStyle;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import vo.export.CorpState_vo;
import vo.export.Info_vo;

public class ExportHandle {
	public static final String[] CORP = { "地区", "月份", "月收入", "量收增幅比", "用户",
			"市场占有率", "净增市场占有率" };

	public static final String[] INFO = { "地区","人口", "GDP", "人均GDP", "支柱产业",
			"地理环境" };

	// private static final String ADVERTISEMENT = "t_advertisement";

	public ExportHandle() {

	}

	/**
	 * 输出Excel
	 * 
	 * @param os
	 */
	public void writeExcel(OutputStream os, String[] item, String tableName,
			List list) {
		try {
			/**
			 * 只能通过API提供的工厂方法来创建Workbook，而不能使用WritableWorkbook的构造函数，
			 * 因为类WritableWorkbook的构造函数为protected类型
			 * method(1)直接从目标文件中读取WritableWorkbook wwb =
			 * Workbook.createWorkbook(new File(targetfile)); method(2)如下实例所示
			 * 将WritableWorkbook直接写入到输出流
			 * 
			 */
			WritableWorkbook wwb = Workbook.createWorkbook(os);
			// 创建Excel工作表 指定名称和位置
			WritableSheet ws = wwb.createSheet(tableName, 0);

			// **************往工作表中添加数据*****************

			// 1.添加Label对象
			WritableFont wf = new WritableFont(WritableFont.TIMES, 10,
					WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE,
					jxl.format.Colour.BLACK);
			WritableFont titlewf = new WritableFont(WritableFont.TIMES, 16,
					WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE,
					jxl.format.Colour.BLACK);
			WritableCellFormat wcf = new jxl.write.WritableCellFormat(wf);
			WritableCellFormat titlewcf = new jxl.write.WritableCellFormat(
					titlewf);
			titlewcf.setAlignment(jxl.format.Alignment.CENTRE);
			wcf.setBackground(jxl.format.Colour.ICE_BLUE);
			wcf.setBorder(Border.ALL, jxl.format.BorderLineStyle.THIN);

			ws.mergeCells(0, 0, item.length - 1, 0);
			ws.addCell(new Label(0, 0, tableName, titlewcf));
			for (int i = 0; i < item.length; i++) {
				Label labelcf = new Label(i, 1, item[i], wcf);
				ws.setColumnView(i, item[i].length() * 3);
				ws.addCell(labelcf);
			}
			Label value;
			for (int i = 0; i < list.size(); i++) {
				if (list.get(i) instanceof CorpState_vo) {
					CorpState_vo cs = (CorpState_vo) list.get(i);
					int x = 1;
					value = new Label(2, i + 2, cs.getDiq());
					ws.addCell(value);

					value = new Label(x++, i + 2, cs.getYuef());
					ws.addCell(value);

					value = new Label(x++, i + 2, cs.getYuesr());
					ws.addCell(value);

					value = new Label(x++, i + 2, cs.getLangzfb());
					ws.addCell(value);

					value = new Label(x++, i + 2, cs.getYongh());
					ws.addCell(value);

					value = new Label(x++, i + 2, cs.getShiChangzyl());
					ws.addCell(value);

					value = new Label(x++, i + 2, cs.getJingsczyl());
					ws.addCell(value);
				}else if(list.get(i) instanceof Info_vo){
					Info_vo info = (Info_vo) list.get(i);
					int info_x = 0;
					value = new Label(info_x, i + 2, info.getDiq());
					ws.addCell(value);

					value = new Label(++info_x, i + 2, info.getRenk());
					ws.addCell(value);
					
					value = new Label(++info_x, i + 2, info.getGDP());
					ws.addCell(value);

					value = new Label(++info_x, i + 2, info.getRenjGDP());
					ws.addCell(value);

					value = new Label(++info_x, i + 2, info.getZhizcy());
					ws.addCell(value);

					value = new Label(++info_x, i + 2, info.getDilhj());
					ws.addCell(value);
				}
			}

			// // 添加带有字体颜色的Formatting对象
			// WritableFont wfc = new WritableFont(WritableFont.ARIAL, 10,
			// WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE,
			// jxl.format.Colour.RED);
			// WritableCellFormat wcfFC = new WritableCellFormat(wfc);
			// Label labelCF = new Label(1, 0, "This is a Label Cell", wcfFC);
			// ws.addCell(labelCF);

			// // 2.添加Number对象
			// Number labelN = new Number(0, 1, 3.1415926);
			// ws.addCell(labelN);
			//	
			// // 添加带有formatting的Number对象
			// NumberFormat nf = new NumberFormat("#.##");
			// WritableCellFormat wcfN = new WritableCellFormat(nf);
			// Number labelNF = new Number(1, 1, 3.1415926, wcfN);
			// ws.addCell(labelNF);
			//	
			// // 3.添加Boolean对象
			// Boolean labelB = new jxl.write.Boolean(0, 2, false);
			// ws.addCell(labelB);

			// 4.添加DateTime对象
			// jxl.write.DateTime labelDT = new jxl.write.DateTime(0, 3,
			// new java.util.Date());
			// ws.addCell(labelDT);

			// 添加带有formatting的DateFormat对象
			// DateFormat df = new DateFormat("dd MM yyyy hh:mm:ss");
			// WritableCellFormat wcfDF = new WritableCellFormat(df);
			// DateTime labelDTF = new DateTime(1, 3, new java.util.Date(),
			// wcfDF);
			// ws.addCell(labelDTF);

			// 添加图片对象,jxl只支持png格式图片
			// File image = new File("f:\\2.png");
			// WritableImage wimage = new WritableImage(0, 1, 2, 2, image);//
			// 0,1分别代表x,y.2,2代表宽和高占的单元格数
			// ws.addImage(wimage);
			// 写入工作表
			wwb.write();
			wwb.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	// 测试
	public static void main(String[] args) {
		try {
			// 读Excel
			// ADHandle ex = new ADHandle();
			// System.out.println("starting....");
			// ArrayList al=ex.readExcel("D:\\work\\etl\\AD.xls");
			// ex.insertData(al);
			// 输出Excel
			File fileWrite = new File("e:\\abc.xls");
			fileWrite.createNewFile();
			OutputStream os = new FileOutputStream(fileWrite);
			ExportHandle ex = new ExportHandle();
			ArrayList al=new ArrayList();
			CorpState_vo vo=new CorpState_vo();
			
			vo.setDiq("广东");
			vo.setJingsczyl("A");
			vo.setLangzfb("B");
			vo.setShiChangzyl("C");
			vo.setYongh("D");
			vo.setYuef("E");
			vo.setYuesr("F");
			
			Info_vo ivo=new Info_vo();
			
			ivo.setGDP("1222");
			ivo.setRenjGDP("22");
			ivo.setRenk("100");
			ivo.setZhizcy("旅游");
			ivo.setDilhj("好");
			ivo.setDiq("大布");
			al.add(ivo);
			ivo.setGDP("1222");
			ivo.setRenjGDP("22");
			ivo.setRenk("100");
			ivo.setZhizcy("旅游");
			ivo.setDilhj("不好");
			ivo.setDiq("仁化");
			al.add(ivo);
			
//			al.add(vo);
			ex.writeExcel(os,ExportHandle.INFO, "宏观信息表", al);
			// 修改Excel
			// ExcelHandle.modifyExcel(new file(""),new File(""));
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

}
